import click
from flask import Flask, jsonify
from flask.cli import with_appcontext
from sqlalchemy import func

from app.api.admin import bp_admin
from app.api.wxapp import bp_wxapp
from app.exception import APIException
from app.extensions import db, migrate, wx
from app.models import UserRecord, User
from config import Config


def create_app(config_class=Config):
    app = Flask(__name__)
    app.config.from_object(config_class)

    # 注册扩展
    wx.init_app(app)
    db.init_app(app)
    migrate.init_app(app, db)

    # 注册蓝图
    app.register_blueprint(bp_admin)
    app.register_blueprint(bp_wxapp)

    # 注册错误处理
    @app.errorhandler(APIException)
    def handle_api_error(error):
        # response 的 json 内容为自定义错误代码和错误信息
        response = jsonify(error.to_dict())

        # response 返回 error 发生时定义的标准错误代码
        response.status_code = error.status_code

        return response

    @app.errorhandler(401)
    def handle_404_error(error):
        response = jsonify({
            'code': 401,
            'message': '没有权限'
        })
        response.status_code = 200
        return response

    @app.errorhandler(403)
    def handle_403_error(error):
        response = jsonify({
            'code': 403,
            'message': '权限错误'
        })
        response.status_code = 200
        return response

    @app.errorhandler(500)
    def handle_500_error(error):
        response = jsonify({
            'code': 500,
            'message': '服务器错误'
        })
        response.status_code = 200
        return response

    # 注册命令
    app.cli.add_command(init_db_command)
    app.cli.add_command(drop_db_command)
    app.cli.add_command(create_user_command)
    app.cli.add_command(test_sql)

    return app


@click.command('init-db')
@with_appcontext
def init_db_command():
    db.create_all()
    # 执行sql语句 ALTER TABLE `t_user` AUTO_INCREMENT = 100000000
    db.session.execute('ALTER TABLE `t_user` AUTO_INCREMENT = 10000001')
    db.session.commit()
    click.echo('初始化数据库.')


@click.command('drop-db')
@with_appcontext
def drop_db_command():
    db.drop_all()
    click.echo('删除数据库.')


@click.command('create-user')
@with_appcontext
def create_user_command():
    from app.models import AdminUser
    user = AdminUser(
        username='admin',
        phone_number='13612345671',
        password='123456',
        real_name='管理员',
        role='admin'
    )
    db.session.add(user)
    db.session.commit()
    click.echo('创建管理员.')


@click.command('test-sql')
@with_appcontext
def test_sql():
    query = UserRecord.query.with_entities(UserRecord.user_id.label('user_id'), User.nickname.label('nickname'), func.sum(UserRecord.doll).label('doll')) \
        .filter_by(type=1).group_by(UserRecord.user_id).outerjoin(User, UserRecord.user_id == User.id)
    # result = query.all()
    sql = 'select a.user_id, b.nickname, sum(a.doll) as doll from t_user_record a ' \
          'left join t_user b on a.user_id = b.id where a.type=1 group by a.user_id'
    print(query)
    result = db.session.execute(sql)
    click.echo(result)
